# -*- coding: utf-8 -*-
import os
import re
import sqlite3
import sys

from ip import *

def getConn():
    return sqlite3.connect("subDomain.db")

def init():
    """ 建表 """
    cx = getConn()
    cu = cx.cursor()
    cu.execute('create table site (sitename varchar(64) UNIQUE, maindomain varchar(64) UNIQUE , siteno integer)')
    cu.execute('create table subdomain(sitename varchar(64), subdomain varchar(64) UNIQUE , subno integer)')
    cu.execute('create table test( roundno varchar(32), subdomain varchar(64) , ip varchar(40) ,result varchar(128), sent varchar(8), received varchar(8), lost varchar(8), min varchar(8), max varchar(8), average varchar(8), stddev varchar(8),isp varchar(16), ispDetail varchar(32), desc varchar(32), flag varchar(1) )')
    cu.execute('create table ipinfo( startIP varchar(32), endDefine varchar(32), isp varchar(16) , ispDetail varchar(32), desc varchar(32))')
    cu.execute('create table sdindex( subdomain varchar(64)  , subno integer UNIQUE)')
    cx.close()

def delDB():
    """ drop db 中所有的表 """
    cx = getConn()
    cu = cx.cursor()
    cu.execute('drop table site')
    cu.execute('drop table subdomain')
    cu.execute('drop table test')
    cu.execute('drop table ipinfo')
    cx.close()



def maintain():
    """
    从 site.txt 中载入所有的待测站点，并存入 site 表中。
    从 subdomain.txt 中载入所有的待测子域名，并存入 subdomain 表中。
    
    """
    cx = getConn()

    siteFile = open('site.txt')
    if siteFile :
        siteList = siteFile.readlines()
        siteFile.close()

        cu = cx.cursor()
        for no,site in enumerate(siteList):
            temp = site.split()
            if len(temp) == 2:
                cu.execute("insert into site (sitename, maindomain, siteno) values('" + temp[0] + "','" + temp[1] + "','" + str(no+1) + "')")
                cx.commit()
        cu.close() 


    subdomainFile = open('subdomain.txt')
    subdomainList = subdomainFile.readlines()

    siteName = ''
    no = 1
    for line in subdomainList :
        temp = line.split()

        if len(temp) == 2 :
            if siteName == temp[0]:
                no += 1
            else:
                no = 1
            siteName = temp[0]
            subDomain = temp[1]

        elif len(temp) == 1 :
            subDomain = temp[0]
            no += 1
        else :
            continue

        cu = cx.cursor()
        cu.execute("select * from subdomain where subdomain='"+ subDomain +"'")
        if cu.fetchone():
            pass
            print subDomain + ' already exist.'
        else:
            cu.execute("insert into subdomain(sitename, subdomain, subno) values('" + siteName + "','" + subDomain + "','" + str(no) + "')")
            cx.commit()
            print subDomain
        cu.close()

    cx.close()

    subdomainFile.close()

def getIP(site):
    ret = {}
    ret['result'] = ''
    ret['ip'] = ''
    ret['sent'] = ''
    ret['received'] = ''
    ret['lost'] = ''
    ret['min'] = ''
    ret['average'] = ''
    ret['max'] = ''
    ret['stddev'] = ''

    cmd = 'ping -w 1000 -n 4 ' + site
    print cmd
    output = os.popen(cmd).readlines()
    for line in output:
        if line == '\r\n':
            output.remove('\r\n')
        #else:
            #print line
    
    # result
    ret['result'] = output[0][:-2]

    # find ip
    pstr = r'[\d]+\.[\d]+\.[\d]+\.[\d]+'
    p=re.compile( pstr )
    tempList = p.findall(output[0])
    if tempList :
        ret['ip'] = tempList[0]
        # is windows or not windows
        if output[-1].find(' ') == 0 :
            # find max min avg
            temp = output[-1].split()
            if temp[0] == 'Minimum' :
                ret['min'] = temp[2][:-3]
                ret['max'] = temp[5][:-3]
                ret['average'] = temp[8][:-2]
                # find sent received lost 
                temp = output[-3].split()
                # print temp
                
            # This bad code because ping result: Request timed out.
            if temp[0] == 'Packets:' :
                ret['sent'] = temp[3][:-1]
                ret['received'] = temp[6][:-1] 
                ret['lost'] = temp[10][1:] 
        elif output[-1].find('round-trip') == 0 :
            # find max min avg
            #print output[-1]
            temp = output[-1].split()
            temp = temp[3].split('/')
            ret['min'] = temp[0]
            ret['average'] = temp[1]
            ret['max'] = temp[2]
            ret['stddev'] = temp[3]
            # find sent received lost
            temp = output[-2].split()
            ret['sent'] = temp[0]
            ret['received'] = temp[3] 
            ret['lost'] = temp[6]
        elif  output[-1].find('transmitted') > 0 :
            # find sent received lost
            temp = output[-1].split()
            ret['sent'] = temp[0]
            ret['received'] = temp[3] 
            ret['lost'] = temp[6]

    print ret
    return ret
    


def test( roundno ):
    """
    先根据 roundno 确定是新开测试还是继续测试。
如果是新开测试，从subdomain表中拷贝一份子域名过来。
如果是继续测试，就不需要了。
    """
    cx = getConn()
    cu = cx.cursor()
    sql = "select * from test where roundno='"+ roundno +"' "
    cu.execute(sql)
    if cu.fetchone():
        print roundno + ' test already init.'
    else:
        cu.execute("insert into test (roundno, subdomain, flag) select '" + roundno + "', subdomain, '0' from subdomain" )
        cx.commit()
        print 'test init ok.'

   
    sql_flag0 = "select * from test where roundno='"+ roundno +"' and flag='0'"
    cu.execute(sql_flag0)
    #sitelist = cu.fetchmany(100)
    #for site in sitelist:
    cu2 = cx.cursor()
    for site in cu:
        ret = getIP(site[1])
        sql = "update test set result='" + ret['result']  \
                + "', ip='" + ret['ip'] \
                + "', sent='" + ret['sent'] \
                + "', received='" + ret['received'] \
                + "', lost='" + ret['lost'] \
                + "', max='" + ret['max'] \
                + "', min='" + ret['min'] \
                + "', average='" + ret['average'] \
                + "', stddev='" + ret['stddev'] \
                + "',flag='1' where roundno='"+ roundno +"' and subdomain='"+ site[1] +"'"
        cu2.execute(sql)
        cx.commit()
    cu2.close()

    cu.close()
    cx.close()

def init_ip():
    ispIpFile = open('isp_ip.txt')
    ispIpList = ispIpFile.readlines()
    ispIpFile.close()
    
    cx = getConn()
    cu = cx.cursor()

    loopNumber = 0

    for info in ispIpList :
        info_array = info.split('/')
        if len(info_array) == 5 :
            startIP = info_array[0].strip()
            endDefine = info_array[1].strip()
            isp = info_array[2].strip()
            ispDetail = info_array[3].strip()
            desc = info_array[4].strip()
    
            sql = "insert into ipinfo (startIP, endDefine, isp, ispDetail, desc) values('" + startIP + "','" + endDefine + "','" + isp + "','" + ispDetail + "','" + desc + "') "
            cu.execute(sql)
            #cx.commit()
            loopNumber = loopNumber + 1
            if loopNumber % 20 == 0 :
                cx.commit()
        else:
            print info
    cx.commit()

    cu.close()
    cx.close()

def analysis():
    cx = getConn()
    cu = cx.cursor()
    sql = "select * from ipinfo"
    cu.execute(sql)

    # 初始化 ip 信息
    ispIpInfoList = []
    for ip in cu:
        #print ip
        ispIpInfo = IpInfo(ip)
        ispIpInfoList.append(ispIpInfo)
        #print ip[1]
    
    cu2 = cx.cursor()
    sql = "select ip,roundno,subdomain from test where flag='1'"
    cu.execute(sql)
    for test in cu:
        print test[0]
        notfoundFlag = True
        if test[0].strip() == '':
            print "no IP"
        else:
            for ipInfo in ispIpInfoList :
                if ipInfo.inThere( test[0] ) :
                    strTemp = test[0] + '\t' + ipInfo.isp + '\t' + ipInfo.ispDetail + '\n'
                    sql2 = "update test set isp='" + ipInfo.isp \
                            + "', ispDetail='" + ipInfo.ispDetail \
                            + "', desc='" + ipInfo.desc \
                            + "', flag='2' where roundno='" + test[1] \
                            + "' and subdomain='" + test[2] + "'"
                    #print sql2
                    cu2.execute(sql2)
                    cx.commit()
                    notfoundFlag = False
                    break

        if notfoundFlag :
            sql2 = "update test set isp='', ispDetail='', desc='' "\
                    + ", flag='2' where roundno='" + test[1] \
                    + "' and subdomain='" + test[2] + "'"
            print sql2
            cu2.execute(sql2)
            cx.commit()
    
    cu2.close()
    cu.close()
    cx.close()

def report(roundno):
    cx = getConn()
    cu = cx.cursor()
    sql = "select b.* from sdindex as a left join test as b on a.subdomain=b.subdomain and roundno='" \
            + roundno + "' order by a.subno"

    reportFile = open('report_'+ roundno +'.txt','w')
    
    cu.execute(sql)
    for test in cu:
        print test[1]
        strTemp = test[1] + '\t' + test[2]  + '\t' + test[11] + '\t' + test[12] \
                + '\t' + test[13]+ '\t' + test[6] + '\t' + test[9]  + '\n'
        if strTemp :
            strTemp = strTemp.encode('utf-8')
        else:
            strTemp = '\n'
        reportFile.write(strTemp) 
    
    reportFile.close()
    cu.close()
    cx.close()

def sdindex():
    subdomainFile = open('subdomain.txt')
    subdomainList = subdomainFile.readlines()
    subdomainFile.close()

    cx = getConn()
    cu = cx.cursor()
    no = 1
    for line in subdomainList :
        if line.strip() :
            cu.execute("insert into sdindex( subdomain, subno) values('" + line.strip() + "','" + str(no) + "')")
            no += 1
            print 'Number:' + str(no)
            if no % 20 == 0 :
                cx.commit()
    cx.commit()

    cu.close()
    cx.close()


def usage():
    print """Usage: python sub_info.py [ analysis | test {roundno} | report {roundno}| maintain| sdindex | init | init_ip ]
    delDB : 危险操作，删除全部数据和表结构
    init : 初始化数据库，就是 Create Table
    init_ip : 维护IP地址归属信息
    maintain : 从文本文件导入全部主域名和子域名
    sdindex : 导入子域名排序规则
    test : 进行测试，需要多带一个参数，用来标明测试的轮次
    analysis : 分析IP地址归属
    
    """



if len(sys.argv) < 2 :
    usage()
else :
    if sys.argv[1] == 'test' :
        if len(sys.argv) == 3 :
            test(sys.argv[2])
        else:
            usage()
    elif sys.argv[1] == 'report' :
        if len(sys.argv) == 3 :
            report(sys.argv[2])
        else:
            usage()
    elif sys.argv[1] == 'maintain' :
        maintain()
    elif sys.argv[1] == 'init' :
        init()
    elif sys.argv[1] == 'init_ip' :
        init_ip()
    elif sys.argv[1] == 'analysis' :
        analysis()
    elif sys.argv[1] == 'delDB' :
        delDB()
    elif sys.argv[1] == 'sdindex' :
        sdindex()
    else :
        usage()



